package hp.com.dao.impl;

import hp.com.dao.BaseDao;
import hp.com.dao.ProductDAO;
import hp.com.entity.PageInfo;
import hp.com.entity.Product;

import java.sql.ResultSet;
import java.sql.SQLException;
import hp.com.entity.Sells;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

public class ProductDAOImpl extends BaseDao implements ProductDAO {

    @Override
    /**
     * 通过商品id来获取商品
     */
    public Product GetProductById(int id) throws Exception {
        Product pd = new Product();
        String sql="select * from product where id=?";
        try {
            ResultSet rs = this.executeQuery(sql,new Object[]{id});
            while(rs.next()){
                pd.setId(id);
                pd.setPname(rs.getString("pname"));
                pd.setDescription(rs.getString("description"));
                pd.setCategory(rs.getString("category"));
                pd.setPicname(rs.getString("picname"));
                pd.setCategoryLevel2(rs.getInt("categoryLevel2"));
                pd.setDetailpics(rs.getString("detailpics"));
                pd.setDetailinformation(rs.getString("detailinformation"));
                pd.setEasyName(rs.getString("easyName"));
                pd.setCateText(getCateTextById(Integer.parseInt(pd.getCategory())));
                pd.setCateLvl2Text(getCateTextById(pd.getCategoryLevel2()));
                return pd;
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    @Override
    public PageInfo<Sells> listPage(PageInfo<Sells> page) throws Exception {
        String sql = "select id,easyName,category,categoryLevel2,description,suk_price,picname from product p,suk_table s where s.`goods_id`=p.`id` group by easyName order by suk_price limit ?,? ;";
        Object[] obj = new Object[2];
        obj[0] = (page.getPageNo() - 1) * page.getPageSize();
        obj[1] = page.getPageSize();
        ResultSet rs = executeQuery(sql, obj);
        ArrayList<Sells> list = new ArrayList<>();
        while (rs.next()){
            Sells sell = new Sells();
            sell.setId(rs.getInt("id"));
            sell.setFullDescription(rs.getString("description"));
            String pics = (rs.getString("picname").split(","))[0];
            sell.setPicName(pics);
            sell.setSukPrice(rs.getString("suk_price"));
            sell.setEasyName(rs.getString("easyName"));
            sell.setCategory(rs.getInt("category"));
            sell.setCateLvl2(rs.getInt("categoryLevel2"));
            sell.setCateLvl2Text(getCateTextById(sell.getCateLvl2()));
            sell.setDescription((sell.getFullDescription().split(" "))[0]);
            list.add(sell);
        }
        page.setPageList(list);
        String sqlCount = "select count(*) total from product";
        ResultSet resultSet = executeQuery(sqlCount, new Object[]{});
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt("total");
        }
        //计算总页数
        int size = page.getPageSize();
        //
        int num = count % size == 0 ? count / size : count / size + 1;

        page.setPageNum(num);
        page.setPageCount(count);

        return page;
    }

    @Override
    public PageInfo<Sells> listPageByGory(PageInfo<Sells> page,int category) throws Exception {
        String sql = "select id,easyName,category,categoryLevel2,description,suk_price,picname from product p,suk_table s where s.`goods_id`=p.`id` and category=? group by easyName order by suk_price limit ?,? ;";
        Object[] obj = new Object[3];
        obj[0] = category;
        obj[1] = (page.getPageNo() - 1) * page.getPageSize();
        obj[2] = page.getPageSize();
        ResultSet rs = executeQuery(sql, obj);
        ArrayList<Sells> list = new ArrayList<>();
        while (rs.next()){
            Sells sell = new Sells();
            sell.setId(rs.getInt("id"));
            sell.setFullDescription(rs.getString("description"));
            String pics = (rs.getString("picname").split(","))[0];
            sell.setPicName(pics);
            sell.setSukPrice(rs.getString("suk_price"));
            sell.setEasyName(rs.getString("easyName"));
            sell.setCategory(rs.getInt("category"));
            sell.setCateLvl2(rs.getInt("categoryLevel2"));
            sell.setCateLvl2Text(getCateTextById(sell.getCateLvl2()));
            sell.setDescription((sell.getFullDescription().split(" "))[0]);
            list.add(sell);
        }
        page.setPageList(list);
        String sqlCount = "select count(*) total from product where category=?";
        ResultSet resultSet = executeQuery(sqlCount, new Object[]{category});
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt("total");
        }
        //计算总页数
        int size = page.getPageSize();
        //
        int num = count % size == 0 ? count / size : count / size + 1;

        page.setPageNum(num);
        page.setPageCount(count);

        return page;
    }

    /**
     * 获取商品的最高价格与最低价格
     * @param id 商品ID
     * @return 最高价格与最低价格
     * @throws Exception
     */
    @Override
    public String GetLowestAndHighestPrice(int id) throws Exception {
        String sql = "select suk_price from suk_table where goods_id=? order by suk_price asc";
        ResultSet rs = this.executeQuery(sql,new Object[]{id});
        String lowest="";String highest="";
        List<Double> prices=new ArrayList<>();
        while(rs.next()){
            //将所有数字添加到prices数组中
            prices.add((double)rs.getDouble("suk_price"));
        }
        DecimalFormat df = new DecimalFormat("#.00");
        lowest=df.format(prices.get(0));
        highest=df.format(prices.get(prices.size()-1));
        if (lowest.equals(highest)){
            return "¥"+lowest;
        }
        return "¥"+lowest+"~¥"+highest;
    }

    /**
     * 通过商品id来获取单一商品Sells对象
     * @param id 商品ID
     * @return 商品Sells对象
     * @throws Exception
     */
    @Override
    public Sells getSellById(int id) throws Exception {
        String sql = "select id,easyName,description,suk_price,picname from product p,suk_table s where s.`goods_id`=p.`id`and s.`goods_id`=? order by suk_price asc ;";
        ResultSet rs = this.executeQuery(sql, new Object[]{id});
        rs.next();
        Sells sell = new Sells();
        sell.setId(rs.getInt("id"));
        sell.setFullDescription(rs.getString("description"));
        String pics = (rs.getString("picname").split(","))[0];
        sell.setPicName(pics);
        sell.setSukPrice(rs.getString("suk_price"));
        sell.setEasyName(rs.getString("easyName"));
        sell.setCategory(rs.getInt("category"));
        sell.setCateLvl2(rs.getInt("categoryLevel2"));
        sell.setDescription((sell.getFullDescription().split(" "))[0]);
        sell.setCateLvl2Text(getCateTextById(sell.getCateLvl2()));

        return sell;
    }

    /**
     * 通过商品ID获取商品的Sells对象集合
     * @param ids 商品id（数组）
     * @return 商品Sells对象集合
     * @throws Exception
     */
    @Override
    public List<Sells> getSellById(int[] ids) throws Exception {
        List<Sells> sells = new ArrayList<>();
        for (int i = 0; i < ids.length; i++) {
            String sql = "select id,easyName,description,suk_price,picname,category,categoryLevel2 from product p,suk_table s where s.`goods_id`=p.`id`and s.`goods_id`=? order by suk_price asc ;";
            ResultSet rs = this.executeQuery(sql, new Object[]{ids[i]});
            rs.next();
            Sells sell = new Sells();
            sell.setId(rs.getInt("id"));
            sell.setFullDescription(rs.getString("description"));
            String pics = (rs.getString("picname").split(","))[0];
            sell.setPicName(pics);
            sell.setSukPrice(rs.getString("suk_price"));
            sell.setEasyName(rs.getString("easyName"));
            sell.setCategory(rs.getInt("category"));
            sell.setCateLvl2(rs.getInt("categoryLevel2"));
            sell.setCateLvl2Text(getCateTextById(sell.getCateLvl2()));
            sell.setDescription((sell.getFullDescription().split(" "))[0]);
            sells.add(sell);
        }
        return sells;
    }

    /**
     * 获取总商品数量
     * @return 总商品数量
     * @throws Exception
     */
    @Override
    public int getProductCount() throws Exception {
        String sql ="select count(*) from product";
        ResultSet rs = this.executeQuery(sql,new Object[]{});
        rs.next();
        return rs.getInt(1);
    }

    /**
     * 获取指定分类的总商品数量
     * @param cate 分类
     * @return 总商品数量
     * @throws Exception
     */
    @Override
    public int getProductCount(int cate) throws Exception {
        String sql = "select count(*) from product where category = ?";
        ResultSet rs =this.executeQuery(sql,new Object[]{cate});
        rs.next();
        return rs.getInt(1);
    }

    /**
     * 通过分类ID获取分类信息明文
     * @param cate 分类ID
     * @return 分类（文字）
     * @throws Exception
     */
    @Override
    public String getCateTextById(int cate) throws Exception {
        String sql = "select typename from product_type where id = ? ";
        ResultSet rs = this.executeQuery(sql,new Object[]{cate});
        rs.next();
        return rs.getString("typename");
    }

    /**
     * 通过一级分类获取该分类的所有商品ID
     * @param cate 分类
     * @return 商品ID集合
     * @throws Exception
     */
    @Override
    public List<Integer> getCategoryIds(String cate) throws Exception {
        String sql = "select id from product where category = ?";
        ResultSet rs = this.executeQuery(sql,new Object[]{cate});
        List<Integer> ids = new ArrayList<>();
        while(rs.next()){
            ids.add(rs.getInt(1));
        }
        return ids;
    }
}
